﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using NLBS.Entity;

namespace NLBS.Data
{
    public partial class AccessDataProvider : DbProvider
    {
        #region User
        public override bool ValidateUser(string username, string password)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT   user_name, user_password FROM blog_User WHERE (user_name = ?) AND (user_password = ?)");
            db.AddInParameter(dbCommand, "username", DbType.String, username);
            db.AddInParameter(dbCommand, "password", DbType.String, password);
            IDataReader dr = db.ExecuteReader(dbCommand);
            if (dr.Read())
            {
                return true;
            }
            return false;
        }

        public override User GetUserByID(int userID)
        {

            DbCommand dbCommand = db.GetSqlStringCommand("SELECT blog_User.*, blog_UserGroup.*,,blog_UserGroup.group_name as groupName FROM (blog_User INNER JOIN blog_UserGroup ON blog_User.user_groupID = blog_UserGroup.group_id) WHERE (blog_User.user_id = ?)");
            db.AddInParameter(dbCommand, "userID", DbType.Int32, userID);

            User user = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    user = CommonDataProvider.PopulateUserViewAndEditFromIDataReader(dataReader);
                    user.Group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                }
            }
            return user;
        }

        public override User GetUserByName(string userName)
        {

            DbCommand dbCommand = db.GetSqlStringCommand("SELECT blog_User.*, blog_UserGroup.*,blog_UserGroup.group_name as groupName FROM (blog_User INNER JOIN blog_UserGroup ON blog_User.user_groupID = blog_UserGroup.group_id) WHERE (blog_User.user_name = ?)");
            db.AddInParameter(dbCommand, "userName", DbType.String, userName);

            User user = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    user = CommonDataProvider.PopulateUserViewAndEditFromIDataReader(dataReader);
                    user.Group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                }
            }
            return user;
        }

        public override void UpdateUser(User user)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("UPDATE  blog_User SET user_name = ?, user_password = ?, user_salt = ?, user_groupID = ?, user_gender = ?, user_email = ?, user_hideEmail = ?, user_homepage = ? WHERE   (user_id = ?)");
            db.AddInParameter(dbCommand, "user_Name", DbType.String, user.UserName);
            db.AddInParameter(dbCommand, "user_password", DbType.String, user.Password);
            db.AddInParameter(dbCommand, "user_salt", DbType.Int32, user.Salt);
            db.AddInParameter(dbCommand, "user_groupID", DbType.Int32, user.GroupID);
            db.AddInParameter(dbCommand, "user_gender", DbType.Int32, user.Gender);
            db.AddInParameter(dbCommand, "user_email", DbType.String, user.Email);
            db.AddInParameter(dbCommand, "user_hideEmail", DbType.Boolean, user.HideEmail);
            db.AddInParameter(dbCommand, "user_homepage", DbType.String, user.Homepage);
            db.AddInParameter(dbCommand, "user_ID", DbType.Int32, user.UserID);
            db.ExecuteNonQuery(dbCommand);
        }

        public override IList<User> GetUser()
        {

            DbCommand dbCommand = db.GetSqlStringCommand("SELECT blog_User.*, blog_UserGroup.*,blog_User.user_groupID as user_groupID,blog_UserGroup.group_name as groupName FROM (blog_User INNER JOIN blog_UserGroup ON blog_User.user_groupID = blog_UserGroup.group_id)");

            IList<User> lists = new List<User>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    User user = CommonDataProvider.PopulateUserViewAndEditFromIDataReader(dataReader);
                    user.Group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                    lists.Add(user);
                }
            }
            return lists;
        }

        public override void InsertUser(User user)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("INSERT INTO `blog_User` (`user_name`, `user_password`, `user_salt`, `user_groupID`, `user_gender`, `user_email`, `user_hideEmail`, `user_homepage`, `user_articleCount`, `user_commentCount`, `user_lastVisit`, `user_ip`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            db.AddInParameter(dbCommand, "user_name", DbType.String, user.UserName);
            db.AddInParameter(dbCommand, "user_password", DbType.String, user.Password);
            db.AddInParameter(dbCommand, "user_salt", DbType.Int32, user.Salt);
            db.AddInParameter(dbCommand, "user_groupID", DbType.Int32, user.GroupID);
            db.AddInParameter(dbCommand, "user_gender", DbType.Int32, user.Gender);
            db.AddInParameter(dbCommand, "user_email", DbType.String, user.Email);
            db.AddInParameter(dbCommand, "user_hideEmail", DbType.Boolean, user.HideEmail);
            db.AddInParameter(dbCommand, "user_homepage", DbType.String, user.Homepage);
            db.AddInParameter(dbCommand, "user_articleCount", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "user_commentCount", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "user_lastVisit", DbType.DateTime, user.LastVisit);
            db.AddInParameter(dbCommand, "user_ip", DbType.String, user.IP);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void DeleteUser(int userID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM blog_User WHERE   (blog_User.user_id = ?)");
            db.AddInParameter(dbCommand, "user_ID", DbType.Int32, userID);
            db.ExecuteNonQuery(dbCommand);
        }

        #endregion
    }
}
